package com.sptus.service.impl;

import com.github.pagehelper.util.StringUtil;
import com.sptus.config.DataLoaderConfig;
import com.sptus.entity.SptUser;
import com.sptus.mapper.master.DevDictionariesMapper;
import com.sptus.service.IAppEquipmentService;
import com.sptus.service.IDevDictionariesService;
import com.sptus.system.domain.vo.AppInfo;
import com.sptus.system.domain.DevDictionaries;
import com.sptus.util.DateUtil;
import com.sptus.util.DevSqlUtil;
import com.sptus.vo.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.util.StringUtils;

import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Description: dev_dictionaries
 * @Author: jeecg-boot
 * @Date: 2024-09-25
 * @Version: V1.0
 */
@Service
public class DevDictionariesServiceImpl extends ServiceImpl<DevDictionariesMapper, DevDictionaries> implements IDevDictionariesService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    IAppEquipmentService equipmentService;


    @Override
    public HashMap<String, Object> getHomeInfo(AppInfo appInfo) {


        DevDictionaries devDictionaries = new DevDictionaries();
        devDictionaries.setCode("BCP");
        devDictionaries.setName("电池soc");
        devDictionaries.setValue("0");
        devDictionaries.setUnit("%");
        devDictionaries.setSN("96142309600264");
        devDictionaries.setRelevanceTable("dev_info_msg0");
        //日太阳能发电
        DevDictionaries devDictionaries1 = new DevDictionaries();
        devDictionaries1.setCode("H00_23");
        devDictionaries1.setName("日太阳能发电");
        devDictionaries1.setValue("0");
        devDictionaries1.setUnit("kWh");
        devDictionaries1.setSN("96142309600264");
        devDictionaries1.setRelevanceTable("dev_info_msg4");
        //负载
        DevDictionaries devDictionaries2 = new DevDictionaries();
        devDictionaries2.setCode("OPT");
        devDictionaries2.setName("负载");
        devDictionaries2.setValue("0");
        devDictionaries2.setUnit("W");
//        devDictionaries2.setAnalysis("{\"valid\":\"2\"}");
//        devDictionaries2.setOperation("");
        devDictionaries2.setSN("96142309600264");
        devDictionaries2.setRelevanceTable("dev_info_msg0");
        //
        DevDictionaries devDictionaries3 = new DevDictionaries();
        devDictionaries3.setCode("SP");
        devDictionaries3.setName("光伏板");
        devDictionaries3.setValue("0");
        devDictionaries3.setUnit("W");
        devDictionaries3.setSN("96142309600264");
        devDictionaries3.setRelevanceTable("dev_info_msg0");

        DevDictionaries devDictionaries9 = new DevDictionaries();
        devDictionaries9.setCode("SP1");
        devDictionaries9.setName("光伏板pv1");
        devDictionaries9.setValue("0");
        devDictionaries9.setUnit("W");
        devDictionaries9.setSN("96142309600264");
        devDictionaries9.setRelevanceTable("dev_info_msg0");

        DevDictionaries devDictionaries8 = new DevDictionaries();
        devDictionaries8.setCode("SP2");
        devDictionaries8.setName("光伏板pv2");
        devDictionaries8.setValue("0");
        devDictionaries8.setUnit("W");
        devDictionaries8.setSN("96142309600264");
        devDictionaries8.setRelevanceTable("dev_info_msg0");

        DevDictionaries devDictionaries5 = new DevDictionaries();
        devDictionaries5.setCode("ICT");
        devDictionaries5.setName("电网");
        devDictionaries5.setValue("0");
        devDictionaries5.setUnit("W");
//        devDictionaries5.setAnalysis("{\"valid\":\"2\"}");
//        devDictionaries5.setOperation("x/1000");
        devDictionaries5.setSN("96142309600264");
        devDictionaries5.setRelevanceTable("dev_info_msg0");


        DevDictionaries devDictionaries4 = new DevDictionaries();
        devDictionaries4.setCode("BCAP");
        devDictionaries4.setName("储能电池");
        devDictionaries4.setValue("0");
        devDictionaries4.setUnit("W");
        devDictionaries4.setSN("96142309600264");
        devDictionaries4.setRelevanceTable("dev_info_msg0");

        DevDictionaries devDictionaries6 = new DevDictionaries();
        devDictionaries6.setCode("BV");
        devDictionaries6.setName("电池电压");
        devDictionaries6.setValue("0");
        devDictionaries6.setUnit("V");
        devDictionaries6.setSN("96142309600264");
        devDictionaries6.setRelevanceTable("dev_info_msg0");

        DevDictionaries devDictionaries7 = new DevDictionaries();
        devDictionaries7.setCode("BC");
        devDictionaries7.setName("电池电流");
        devDictionaries7.setValue("0");
        devDictionaries7.setUnit("A");
        devDictionaries7.setSN("96142309600264");
        devDictionaries7.setRelevanceTable("dev_info_msg0");


        ArrayList<DevDictionaries> devDictionariesList = new ArrayList<>();
        devDictionariesList.add(devDictionaries);
        devDictionariesList.add(devDictionaries1);
        devDictionariesList.add(devDictionaries2);
        devDictionariesList.add(devDictionaries6);
        devDictionariesList.add(devDictionaries7);
        devDictionariesList.add(devDictionaries8);
        devDictionariesList.add(devDictionaries9);
        devDictionariesList.add(devDictionaries5);

        DevSqlUtil.DevDataSql(devDictionariesList, jdbcTemplate, appInfo.getSn());

        devDictionaries1.setCode("D01_31");
        devDictionaries1.setUnit("$");
        devDictionaries2.setCode("LPT");
        devDictionariesList.add(devDictionaries3);
        devDictionariesList.add(devDictionaries4);
        String dayValue = devDictionaries1.getValue();
        String[] split = dayValue.split(",");
        System.out.println();
        int i1 =0;
        for (int i = 0; i < split.length; i++) {
             i1 += Integer.parseInt(split[i]);
        };




        DecimalFormat df = new DecimalFormat("#0.00"); // 保留两位小数
        //太阳能发电
        Map<String, String> globalData = DataLoaderConfig.getGlobalData();
        String formattedNumber = df.format((double) i1/1000*Double.parseDouble(DataLoaderConfig.getGlobalData().get("spt.electricity")));
        devDictionaries1.setValue(formattedNumber);

        //电网

        if(!StringUtils.isEmpty( devDictionaries5.getValue())){
            if(  (Math.abs(Integer.parseInt( devDictionaries5.getValue()))+"").length()>=4){
                devDictionaries5.setValue(df.format( (double) Integer.parseInt(devDictionaries5.getValue())/1000));
                devDictionaries5.setUnit("kW");
            }

        }

        //负载
        if(!StringUtils.isEmpty( devDictionaries2.getValue())){
            if(  (Math.abs(Integer.parseInt( devDictionaries2.getValue()))+"").length()>=4){
                devDictionaries2.setValue(df.format( (double) Integer.parseInt(devDictionaries2.getValue())/1000));
                devDictionaries2.setUnit("kW");
            }
        }




//        String format = df.format();

//        String format1 = df.format(((double) (Integer.parseInt(devDictionaries8.getValue()) + Integer.parseInt(devDictionaries9.getValue()))));
        int s = Integer.parseInt(devDictionaries8.getValue()) + Integer.parseInt(devDictionaries9.getValue()) ;

        //光伏
        if(s>1000){
            String format = df.format((double) s / 1000);
            devDictionaries3.setValue(format);
            devDictionaries3.setUnit("kW");
            devDictionaries9.setValue(format);
            devDictionaries9.setUnit("kW");
        }else {
            devDictionaries3.setValue(s+"");
            devDictionaries9.setValue(s+"");
        }

        //电池

        int i = (Integer.parseInt(devDictionaries7.getValue()) * Integer.parseInt(devDictionaries6.getValue())) / 100;
        String b = devDictionaries4.getValue();
        if(i>=1000){
            devDictionaries4.setValue(df.format(i/1000));
            devDictionaries4.setUnit("kW");
        }else {
            devDictionaries4.setValue(i+"");
        }


        HashMap<String, Object> objectObjectHashMap = new HashMap<>();
        objectObjectHashMap.put("status", 1);
        objectObjectHashMap.put("update", new Date());
        objectObjectHashMap.put("DataList", devDictionariesList);
        return objectObjectHashMap;
    }

    @Override
    public HashMap<String, ArrayList<DevDictionaries>> getAllData(AppInfo appInfo) {
        ArrayList<DevDictionaries> devDictionaries = new ArrayList<>();
        for (int i = 0; i < 8; i++) {
            DevDictionaries devDictionariesObj = new DevDictionaries();
            devDictionariesObj.setCode("BAlone"+(i+1));
            devDictionariesObj.setRelevanceTable("dev_info_msg6");
            devDictionaries.add(devDictionariesObj);
        }
//        获取电池数据
        List<DevDictionaries> devDictionariesList = DevSqlUtil.DevDataSql(devDictionaries, jdbcTemplate, appInfo.getSn());
        HashMap<String, ArrayList<DevDictionaries>> stringArrayListHashMap = new HashMap<>();
        devDictionariesList.forEach(e->{
                    ArrayList<DevDictionaries> devDictionaries1 = allDevData(e.getValue(), e.getCode());
                  stringArrayListHashMap.put(e.getCode(),devDictionaries1);
                }
        );
        return stringArrayListHashMap;
    }

    @Override
    public Result<Object> getHistoryData(AppInfo appInfo, SptUser sptUser) throws ParseException {
        String def="0";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String tmSql= "\tSELECT @a:=@a + 1 AS `dateTime` FROM mysql.help_topic,(SELECT @a:=0) temp LIMIT 12 \n" +
                ") t1";
        String timeL="%m";
        if(!appInfo.getDateType().equals("0")&&(StringUtils.isEmpty(appInfo.getStartTime())||StringUtils.isEmpty(appInfo.getStartTime()) )){
            Result<Object> ok = Result.ok();
            ok.setMessage("Please enter the time");
            return ok;
        }
        if (appInfo.getDateType().equals("0")){
            Date parse = sdf.parse(appInfo.getStartTime());
            long time = parse.getTime();
            long end= time+24*60*60*1000;
            String format1 = sdf.format(end);
            appInfo.setEndTime(format1);
        }else  if (appInfo.getDateType().equals("1")){
//            appInfo.setStartTime(DateUtil.startWeek());
//            appInfo.setEndTime(DateUtil.endtWeek());
        }else  if (appInfo.getDateType().equals("2")){
//            Map monthTime = DateUtil.getMonthTime();
//            appInfo.setStartTime(monthTime.get("startDate")+"");
//            appInfo.setEndTime(monthTime.get("endDate")+"");
        }else  if (appInfo.getDateType().equals("3")){
//            appInfo.setStartTime(DateUtil.getBeginDayOfYearStr());
//            appInfo.setEndTime(DateUtil.getEndDayOfYearStr());
        }else if(!appInfo.getDateType().equals("0")&& appInfo.getDateType().equals("4")){
            Date start = sdf.parse(appInfo.getStartTime());
            Date end = sdf.parse( appInfo.getEndTime());
            long l = end.getTime() - start.getTime();
            long l1 = l / (24 * 60 * 60 * 1000);
            if(l1>30){
                tmSql="\tSELECT LEFT ( date_add( DATE_SUB('"+appInfo.getStartTime()+"', INTERVAL 1 month ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) month ), 7) dateTime\n" +
                        "\tFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('"+appInfo.getEndTime()+"',date_sub( '"+appInfo.getStartTime()+"', INTERVAL 1 month ))/30\n" +
                        ") t1\n" ;
                def="1";
                timeL="%Y-%m";
                appInfo.setDateType("3");
            }else if (l1>7){
                def="1";
                appInfo.setDateType("2");
            }
        }

        // 保留两位小数
        List<DevDictionaries> devDictionaries = new ArrayList<>();
        ArrayList<String> strings = new ArrayList<>();
        DecimalFormat df = new DecimalFormat("#0.00");
        if(appInfo.getCode().equals("3")){
            if(appInfo.getDateType().equals("0")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("H00_23");
                devDictionariesBCP.setRelevanceTable("dev_info_msg4");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                devDictionaries = DevSqlUtil.DevDataSql(devDictionaries, jdbcTemplate, appInfo.getSn());
                devDictionaries.get(0).setValues(Arrays.asList(devDictionaries.get(0).getValue().split(",")));
                String[] split = devDictionaries.get(0).getValue().split(",");
                double num =0;
                ArrayList<String> values = new ArrayList<>();
                for (int i = 0; i < split.length; i++) {
                    num=num+Integer.parseInt(split[i])*0.001;
                    values.add(df.format(Integer.parseInt(split[i])*0.001));
                }



//                devDictionaries.get(0).setValues(values);
                devDictionaries.get(0).setValue(df.format(num));
                DevDictionaries devDictionariesDate = new DevDictionaries();
                devDictionariesDate.setCode("create_time");
                devDictionariesDate.setRelevanceTable("dev_info_msg0_history");
                for (int i = 0; i < 24; i++) {
                    strings.add(i+"");
                }

                String sql="WITH RECURSIVE time_points AS (\n" +
                        "    SELECT\n" +
                        "        CAST('00:00' AS TIME) AS time\n" +
                        "    UNION ALL\n" +
                        "    SELECT\n" +
                        "        DATE_ADD(time, INTERVAL 5 MINUTE)\n" +
                        "    FROM\n" +
                        "        time_points\n" +
                        "    WHERE\n" +
                        "        time < '23:55'\n" +
                        ")\n" +
                        "SELECT\n" +
                        " DATE_FORMAT(time_points.time,'%H:%i') as dateTime ,IFNULL(a.BCP,0) as BCP\n" +
                        "FROM\n" +
                        "    time_points LEFT JOIN (select\n" +
                        "DATE_FORMAT(from_unixtime(UNIX_TIMESTAMP(create_time) - UNIX_TIMESTAMP(create_time) % 300),'%H:%i') as time,\n" +
                        "   FORMAT((avg(sp1)+avg(sp2))/1000,2)  as BCP\n" +
                        "from dev_info_msg0_history\n" +
                        "WHERE create_time  between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and sn='"+appInfo.getSn()+"'\n" +
                        "group by time\n" +
                        ") as a\n" +
                        "on a.time =DATE_FORMAT(time_points.time,'%H:%i')\n" +
                        "\n";
                devDictionaries=new ArrayList<DevDictionaries>();
                DevDictionaries devDictionariesBCP1 = new DevDictionaries();
                devDictionariesBCP1.setCode("BCP");

                devDictionariesBCP1.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP1.setUnit("kW");
                devDictionaries.add(devDictionariesBCP1);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
//                devDictionaries.get(0).setValues(values);
                devDictionaries.get(0).setValue(df.format(num)+"kWh");
//                devDictionariesDate.setValues(strings);
//                devDictionaries.add(devDictionariesDate);
            }else  if(appInfo.getDateType().equals("2")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("D01_31");
                devDictionariesBCP.setRelevanceTable("dev_info_msg4");
                devDictionariesBCP.setUnit("kWh");
                devDictionaries.add(devDictionariesBCP);
                devDictionaries = DevSqlUtil.DevDataSql(devDictionaries, jdbcTemplate, appInfo.getSn());

                devDictionaries.get(0).setValues(Arrays.asList(devDictionaries.get(0).getValue().split(",")));
                String[] split = devDictionaries.get(0).getValue().split(",");
                double num =0;
                ArrayList<String> values = new ArrayList<>();
                for (int i = 0; i < split.length; i++) {
                    num=num+Integer.parseInt(split[i])*0.001;
                    values.add(df.format(Integer.parseInt(split[i])*0.001));
                }
                devDictionaries.get(0).setValues(values);
                devDictionaries.get(0).setValue(df.format(num)+"kWh");
                DevDictionaries devDictionariesDate = new DevDictionaries();

                devDictionariesDate.setCode("create_time");
                devDictionariesDate.setRelevanceTable("dev_info_msg0_history");
                for (int i = 1; i < DateUtil.getMoneyDay()+1; i++) {
                    strings.add(i+"");
                }
                devDictionariesDate.setValues(strings);
                devDictionaries.add(devDictionariesDate);
                devDictionaries = new ArrayList<>();
                devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BCP");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                String sql="SELECT  t1.dateTime ,IFNULL(a.BW,0) as BCP FROM (\n" +
                        "\tSELECT RIGHT ( date_add( DATE_SUB('"+appInfo.getStartTime()+"', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTime\n" +
                        "\tFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('"+appInfo.getEndTime()+"',date_sub( '"+appInfo.getStartTime()+"', INTERVAL 1 DAY ))\n" +
                        ") t1\n" +
                        "LEFT JOIN\n" +
                        "(\tSELECT FORMAT((avg(sp1)+avg(sp2))/1000,2) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' \n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                System.out.println(sql);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
                devDictionaries.get(0).setValue(df.format(num)+"kWh");

            }else  if(appInfo.getDateType().equals("3")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("M01_12");
                devDictionariesBCP.setRelevanceTable("dev_info_msg4");
                devDictionariesBCP.setUnit("kWh");
                devDictionaries.add(devDictionariesBCP);
                devDictionaries = DevSqlUtil.DevDataSql(devDictionaries, jdbcTemplate, appInfo.getSn());
                devDictionaries.get(0).setValues(Arrays.asList(devDictionaries.get(0).getValue().split(",")));
                String[] split = devDictionaries.get(0).getValue().split(",");
                double num =0;
                ArrayList<String> values = new ArrayList<>();
                for (int i = 0; i < split.length; i++) {
                    num=num+Integer.parseInt(split[i])*0.001;
                    values.add(df.format(Integer.parseInt(split[i])*0.001));
                }


                devDictionaries.get(0).setValue(df.format(num)+"kWh");
                DevDictionaries devDictionariesDate = new DevDictionaries();
                devDictionariesDate.setCode("create_time");
                devDictionariesDate.setRelevanceTable("dev_info_msg0_history");
                for (int i = 1; i < 13; i++) {
                    strings.add(i+".0");
                }
                devDictionaries.add(devDictionariesDate);
                devDictionariesDate.setValues(strings);

                devDictionaries = new ArrayList<>();
                devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BCP");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                String sql="SELECT t1.dateTime,IFNULL(a.BW,0) as BCP FROM (\n" +
                        tmSql+
                        " LEFT JOIN\n" +
                        "(\tSELECT   FORMAT((avg(sp1)+avg(sp2))/1000,2) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"'  \n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
                devDictionaries.get(0).setValue(df.format(num)+"kWh");

            }
        }else if(appInfo.getCode().equals("2")) {
            if(appInfo.getDateType().equals("0")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BCP");

                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("%");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
//                devDictionariesTime.setCode("DATE_FORMAT(create_time,'%H:%i')");
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
//                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
                String sql="WITH RECURSIVE time_points AS (\n" +
                        "    SELECT\n" +
                        "        CAST('00:00' AS TIME) AS time\n" +
                        "    UNION ALL\n" +
                        "    SELECT\n" +
                        "        DATE_ADD(time, INTERVAL 5 MINUTE)\n" +
                        "    FROM\n" +
                        "        time_points\n" +
                        "    WHERE\n" +
                        "        time < '23:55'\n" +
                        ")\n" +
                        "SELECT\n" +
                        " DATE_FORMAT(time_points.time,'%H:%i') as dateTime ,IFNULL(a.BCP,0) as BCP\n" +
                        "FROM\n" +
                        "    time_points LEFT JOIN (select\n" +
                        "DATE_FORMAT(from_unixtime(UNIX_TIMESTAMP(create_time) - UNIX_TIMESTAMP(create_time) % 300),'%H:%i') as time,\n" +
                        "  FORMAT(AVG(BCP),0) as BCP\n" +
                        "from dev_info_msg0_history\n" +
                        "WHERE create_time  between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and sn='"+appInfo.getSn()+"'\n" +
                        "group by time\n" +
                        ") as a\n" +
                        "on a.time =DATE_FORMAT(time_points.time,'%H:%i')\n" +
                        "\n";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);

            }else  if(appInfo.getDateType().equals("2")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BCP");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("%");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                String sql="SELECT  t1.dateTime ,IFNULL(a.BW,0) as BCP FROM (\n" +
                        "\tSELECT RIGHT ( date_add( DATE_SUB('"+appInfo.getStartTime()+"', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTime\n" +
                        "\tFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('"+appInfo.getEndTime()+"',date_sub( '"+appInfo.getStartTime()+"', INTERVAL 1 DAY ))\n" +
                        ") t1\n" +
                        "LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(BCP),0) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' \n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                System.out.println(sql);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else  if(appInfo.getDateType().equals("3")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BCP");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("%");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                String sql="SELECT t1.dateTime,IFNULL(a.BW,0) as BCP FROM (\n" +
                        tmSql+
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(BCP),0) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"'  \n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else if(appInfo.getDateType().equals("4")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BCP");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("%");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("DATE_FORMAT(create_time,'%m-%d %H:%i')");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
            }
        }else if(appInfo.getCode().equals("1")){
            if(appInfo.getDateType().equals("0")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BW");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);

                String sql="WITH RECURSIVE time_points AS (\n" +
                        "    SELECT\n" +
                        "        CAST('00:00' AS TIME) AS time\n" +
                        "    UNION ALL\n" +
                        "    SELECT\n" +
                        "        DATE_ADD(time, INTERVAL 5 MINUTE)\n" +
                        "    FROM\n" +
                        "        time_points\n" +
                        "    WHERE\n" +
                        "        time < '23:55'\n" +
                        ")\n" +
                        "SELECT\n" +
                        " DATE_FORMAT(time_points.time,'%H:%i') as dateTime ,IFNULL(a.BCP,0) as BW\n" +
                        "FROM\n" +
                        "    time_points LEFT JOIN (select\n" +
                        "DATE_FORMAT(from_unixtime(UNIX_TIMESTAMP(create_time) - UNIX_TIMESTAMP(create_time) % 300),'%H:%i') as time,\n" +
                        "  FORMAT(-1*AVG(BV*BC/100)/1000,2) as BCP\n" +
                        "from dev_info_msg0_history\n" +
                        "WHERE create_time  between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and sn='"+appInfo.getSn()+"'\n" +
                        "group by time\n" +
                        ") as a\n" +
                        "on a.time =DATE_FORMAT(time_points.time,'%H:%i')\n" +
                        "\n";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);


//                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
            }else  if(appInfo.getDateType().equals("2")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BW1");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionariesBCP.setAggregation("");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                DevDictionaries devDictionariesBCP1 = new DevDictionaries();
                devDictionariesBCP1.setCode("BW2");
                devDictionariesBCP1.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP1.setUnit("kW");
                devDictionariesBCP1.setAggregation("");
                devDictionaries.add(devDictionariesBCP1);
                String sql="SELECT t1.dateTime ,IFNULL(a.BW,0) as BW1,IFNULL(a2.BW,0) as BW2 FROM (\n" +
                        "\tSELECT RIGHT ( date_add( DATE_SUB('"+appInfo.getStartTime()+"', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTime\n" +
                        "\tFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('"+appInfo.getEndTime()+"',date_sub( '"+appInfo.getStartTime()+"', INTERVAL 1 DAY ))\n" +
                        ") t1\n" +
                        "LEFT JOIN\n" +
                        "(\tSELECT FORMAT(-1*AVG(BV*BC/100)/1000,2) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and BC>0\n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime"+
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(-1*AVG(BV*BC/100)/1000,2) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and BC<0\n" +
                        "GROUP BY dateTime )as a2\n" +
                        "\n" +
                        "on a2.dateTime=t1.dateTime";
                System.out.println(sql);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);

            }else  if(appInfo.getDateType().equals("3")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("BW1");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionariesBCP.setAggregation("");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                DevDictionaries devDictionariesBCP1 = new DevDictionaries();
                devDictionariesBCP1.setCode("BW2");
                devDictionariesBCP1.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP1.setUnit("kW");
                devDictionariesBCP1.setAggregation("");
                devDictionaries.add(devDictionariesBCP1);
                String sql="SELECT t1.dateTime,IFNULL(a.BW,0) as BW1,IFNULL(a2.BW,0) as BW2  FROM (\n" +
                        tmSql+
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(-1*AVG(BV*BC/100)/1000,2) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and BC>0\n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                sql+= " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(-1*AVG(BV*BC/100000),2) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and BC<0\n" +
                        "GROUP BY dateTime )as a2\n" +
                        "\n" +
                        "on a2.dateTime=t1.dateTime";
                System.out.println(sql);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else if(appInfo.getDateType().equals("4")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("FORMAT(BV*BC/100000,2)");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("DATE_FORMAT(create_time,'%m-%d %H:%i')");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
            }


        }else if(appInfo.getCode().equals("4")){
            if(appInfo.getDateType().equals("0")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("OCT");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
//                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
                String sql="WITH RECURSIVE time_points AS (\n" +
                        "    SELECT\n" +
                        "        CAST('00:00' AS TIME) AS time\n" +
                        "    UNION ALL\n" +
                        "    SELECT\n" +
                        "        DATE_ADD(time, INTERVAL 5 MINUTE)\n" +
                        "    FROM\n" +
                        "        time_points\n" +
                        "    WHERE\n" +
                        "        time < '23:55'\n" +
                        ")\n" +
                        "SELECT\n" +
                        " DATE_FORMAT(time_points.time,'%H:%i') as dateTime ,IFNULL(a.BCP,0) as OCT\n" +
                        "FROM\n" +
                        "    time_points LEFT JOIN (select\n" +
                        "DATE_FORMAT(from_unixtime(UNIX_TIMESTAMP(create_time) - UNIX_TIMESTAMP(create_time) % 300),'%H:%i') as time,\n" +
                        "  FORMAT(AVG(OCT)/1000,2) as BCP\n" +
                        "from dev_info_msg0_history\n" +
                        "WHERE create_time  between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and sn='"+appInfo.getSn()+"'\n" +
                        "group by time\n" +
                        ") as a\n" +
                        "on a.time =DATE_FORMAT(time_points.time,'%H:%i')\n" +
                        "\n";

                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else  if(appInfo.getDateType().equals("2")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("OCT");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                String sql="SELECT t1.dateTime ,IFNULL(a.BW,0) as OCT FROM (\n" +
                        "\tSELECT RIGHT ( date_add( DATE_SUB('"+appInfo.getStartTime()+"', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTime\n" +
                        "\tFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('"+appInfo.getEndTime()+"',date_sub( '"+appInfo.getStartTime()+"', INTERVAL 1 DAY ))\n" +
                        ") t1\n" +
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(OCT)/1000,2) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' \n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else  if(appInfo.getDateType().equals("3")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("OCT");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                String sql="SELECT t1.dateTime,IFNULL(a.BW,0) as OCT  FROM (\n" +
                        tmSql+
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(OCT)/1000,2) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' \n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                System.out.println(sql);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else if(appInfo.getDateType().equals("4")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("FORMAT(OCT/1000,2)");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("DATE_FORMAT(create_time,'%m-%d %H:%i')");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
            }


        }else if(appInfo.getCode().equals("5")){
            if(appInfo.getDateType().equals("0")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("ICT");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
//                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
                String sql="WITH RECURSIVE time_points AS (\n" +
                        "    SELECT\n" +
                        "        CAST('00:00' AS TIME) AS time\n" +
                        "    UNION ALL\n" +
                        "    SELECT\n" +
                        "        DATE_ADD(time, INTERVAL 5 MINUTE)\n" +
                        "    FROM\n" +
                        "        time_points\n" +
                        "    WHERE\n" +
                        "        time < '23:55'\n" +
                        ")\n" +
                        "SELECT\n" +
                        " DATE_FORMAT(time_points.time,'%H:%i') as dateTime ,IFNULL(a.BCP,0) as ICT\n" +
                        "FROM\n" +
                        "    time_points LEFT JOIN (select\n" +
                        "DATE_FORMAT(from_unixtime(UNIX_TIMESTAMP(create_time) - UNIX_TIMESTAMP(create_time) % 300),'%H:%i') as time,\n" +
                        "  FORMAT(AVG(ICT)/1000,2) as BCP\n" +
                        "from dev_info_msg0_history\n" +
                        "WHERE create_time  between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and sn='"+appInfo.getSn()+"'\n" +
                        "group by time\n" +
                        ") as a\n" +
                        "on a.time =DATE_FORMAT(time_points.time,'%H:%i')\n" +
                        "\n";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else if(appInfo.getDateType().equals("2")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("ICT1");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionariesBCP.setAggregation("");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                DevDictionaries devDictionariesBCP1 = new DevDictionaries();
                devDictionariesBCP1.setCode("ICT2");
                devDictionariesBCP1.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP1.setUnit("kW");
                devDictionariesBCP1.setAggregation("");
                devDictionaries.add(devDictionariesBCP1);
                String sql="SELECT t1.dateTime ,IFNULL(a.BW,0) as ICT1,IFNULL(a2.BW,0) as ICT2  FROM (\n" +
                        "\tSELECT RIGHT ( date_add( DATE_SUB('"+appInfo.getStartTime()+"', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ), 5 ) dateTime\n" +
                        "\tFROM mysql.help_topic WHERE help_topic_id < DATEDIFF('"+appInfo.getEndTime()+"',date_sub( '"+appInfo.getStartTime()+"', INTERVAL 1 DAY ))\n" +
                        ") t1\n" +
                        "LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(ICT)/1000,2) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and ICT<0\n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime"+
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(ICT)/1000,2) BW ,DATE_FORMAT(create_time, '%m-%d') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and ICT>0\n" +
                        "GROUP BY dateTime )as a2\n" +
                        "\n" +
                        "on a2.dateTime=t1.dateTime";

                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else if(appInfo.getDateType().equals("3")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("ICT1");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionariesBCP.setAggregation("");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("dateTime");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                DevDictionaries devDictionariesBCP1 = new DevDictionaries();
                devDictionariesBCP1.setCode("ICT2");
                devDictionariesBCP1.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP1.setUnit("kW");
                devDictionariesBCP1.setAggregation("");
                devDictionaries.add(devDictionariesBCP1);
                String sql="SELECT t1.dateTime,IFNULL(a.BW,0) as ICT1,IFNULL(a2.BW,0) as ICT2  FROM (\n" +
                        tmSql+
                        " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(ICT)/1000,2) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and ICT<0\n" +
                        "GROUP BY dateTime )as a\n" +
                        "\n" +
                        "on a.dateTime=t1.dateTime";
                sql+= " LEFT JOIN\n" +
                        "(\tSELECT FORMAT(AVG(ICT)/1000,2) BW ,DATE_FORMAT(create_time, '"+timeL+"') dateTime\n" +
                        "FROM dev_info_msg0_history where sn='"+appInfo.getSn()+"' and create_time between '"+appInfo.getStartTime()+"' and '"+appInfo.getEndTime()+"' and ICT>0\n" +
                        "GROUP BY dateTime )as a2\n" +
                        "\n" +
                        "on a2.dateTime=t1.dateTime";
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, jdbcTemplate,sql);
            }else if(appInfo.getDateType().equals("4")){
                DevDictionaries devDictionariesBCP = new DevDictionaries();
                devDictionariesBCP.setCode("FORMAT(ICT/1000,2)");
                devDictionariesBCP.setRelevanceTable("dev_info_msg0_history");
                devDictionariesBCP.setUnit("kW");
                devDictionaries.add(devDictionariesBCP);
                DevDictionaries devDictionariesTime = new DevDictionaries();
                devDictionariesTime.setCode("DATE_FORMAT(create_time,'%m-%d %H:%i')");
                devDictionariesTime.setRelevanceTable("dev_info_msg0_history");
                devDictionaries.add(devDictionariesTime);
                devDictionaries = DevSqlUtil.DevDataHistorySql(devDictionaries, appInfo.getStartTime(), appInfo.getEndTime(), jdbcTemplate, appInfo.getSn());
            }

        }
        if(devDictionaries.size()>0){
            devDictionaries.get(0).setIsShow(def);
        }

        if(appInfo.getDateType().equals("3")&& def.equals("0")){
            ArrayList<String> mos = new ArrayList<>();
            mos.add("Jan");
            mos.add("Feb");
            mos.add("Mar");
            mos.add("Apr");
            mos.add("May");
            mos.add("Jun");
            mos.add("Jul");
            mos.add("Aug");
            mos.add("Sep");
            mos.add("Oct");
            mos.add("Nov");
            mos.add("Dec");
            devDictionaries.get(1).setValues(mos);
        }
        Result<Object> ok = Result.ok();

        ok.setResult(devDictionaries);
        return ok;
    }

    public  ArrayList<DevDictionaries> allDevData(String string,String name){
        if(StringUtil.isEmpty(string)){
            return null;
        }
        String[] split = string.split(";");
        ArrayList<DevDictionaries> devDictionariesList = new ArrayList<>();
        for (int i = 0; i < split.length; i++) {
            DevDictionaries devDictionaries = new DevDictionaries();
            devDictionaries.setCode(name+"_code_"+i);
            if(i>=10 && i<=32){

                devDictionaries.setValue(split[i].equals("0")?"OFF":"ON");
            }else {
                devDictionaries.setValue(split[i]);
            }

            devDictionaries.setState(1+"");
            devDictionariesList.add(devDictionaries);
        }

        DevDictionaries devDictionariesBMSSoftware= devDictionariesList.get(0);
        devDictionariesBMSSoftware.setName("BMS Software Version");
        devDictionariesBMSSoftware.setUnit("");


        DevDictionaries devDictionariesBMS= devDictionariesList.get(1);
        devDictionariesBMS.setName("BMS Hardware Version");
        devDictionariesBMS.setUnit("");

        //BV
        DevDictionaries devDictionariesBV = devDictionariesList.get(2);
        devDictionariesBV.setName("BV");
        devDictionariesBV.setUnit("V");
        devDictionariesBV.setOperation("x/10");
        //SOC
        DevDictionaries devDictionariesSoc = devDictionariesList.get(3);
        devDictionariesSoc.setName("SOC");
        devDictionariesSoc.setUnit("%");

        //SOH
        DevDictionaries devDictionariesSOH = devDictionariesList.get(4);
        devDictionariesSOH.setName("SOH");
        devDictionariesSOH.setUnit("%");

        //充电电流
        DevDictionaries devDictionariesICHG = devDictionariesList.get(5);
        devDictionariesICHG.setName("charge current");
        devDictionariesICHG.setUnit("A");
        devDictionariesICHG.setOperation("x/10");

        //放电电流
        DevDictionaries devDictionariesIMAX = devDictionariesList.get(6);
        devDictionariesIMAX.setName("discharge current");
        devDictionariesIMAX.setUnit("A");
        devDictionariesIMAX.setOperation("x/10");

        DevDictionaries devDictionariesNUM = devDictionariesList.get(7);
        devDictionariesNUM.setName("Number of battery cycles");
        devDictionariesNUM.setUnit("");

        DevDictionaries devDictionariesNUME = devDictionariesList.get(8);
        devDictionariesNUME.setName("num");
        devDictionariesNUME.setUnit("");

        //电池容量
        DevDictionaries devDictionariesMAH = devDictionariesList.get(9);
        devDictionariesMAH.setName("battery capacity");
        devDictionariesMAH.setUnit("mAh");

        //总体过压保护状态
        DevDictionaries devDictionariesDOPS = devDictionariesList.get(10);
        devDictionariesDOPS.setName("Overall overvoltage protection state");

        //总体欠压保护状态
        DevDictionaries devDictionariesDUPS = devDictionariesList.get(11);
        devDictionariesDUPS.setName("Overall undervoltage protection state");

        //单体过压保护状态
        DevDictionaries devDictionariesCOP = devDictionariesList.get(12);
        devDictionariesCOP.setName("Cell overvoltage protection state");

        //单体欠压保护状态
        DevDictionaries devDictionariesCUP = devDictionariesList.get(13);
        devDictionariesCUP.setName("Cell undervoltage protection state");

        //充电电流保护状态
        DevDictionaries devDictionariesCCP = devDictionariesList.get(14);
        devDictionariesCCP.setName("Charging current protection state");

        //放电电流保护状态
        DevDictionaries devDictionariesDCP = devDictionariesList.get(15);
        devDictionariesDCP.setName("Discharge current protection state");

        //总体充电温度保护状态
        DevDictionaries devDictionariesOCTP = devDictionariesList.get(16);
        devDictionariesOCTP.setName("Overall charging temperature protection state");


        //总体放电温度保护状态
        DevDictionaries devDictionariesODTP = devDictionariesList.get(17);
        devDictionariesODTP.setName("Overall discharge temperature protection state");

        //电芯充电温度保护状态
        DevDictionaries devDictionariesBCTP = devDictionariesList.get(18);
        devDictionariesBCTP.setName("Battery charging temperature protection state");

        //电芯放电温度保护状态
        DevDictionaries devDictionariesBDTP = devDictionariesList.get(19);
        devDictionariesBDTP.setName("Battery discharge temperature protection state");

        //MOS温度保护状态
        DevDictionaries devDictionariesMTP = devDictionariesList.get(20);
        devDictionariesMTP.setName("MOS Temperature protection state");

        //短路保护状态
        DevDictionaries devDictionariesSCP = devDictionariesList.get(21);
        devDictionariesSCP.setName("short-circuit protection state");

        //总体过压告警状态
        DevDictionaries devDictionariesOOA = devDictionariesList.get(22);
        devDictionariesOOA.setName("Overall overvoltage alarm state");

        //总体欠压告警状态
        DevDictionaries devDictionariesOUA = devDictionariesList.get(23);
        devDictionariesOUA.setName("Overall undervoltage alarm state");

        //单体过压告警状态
        DevDictionaries devDictionariesCUA = devDictionariesList.get(24);
        devDictionariesCUA.setName("Cell overvoltage alarm state");

        //单体欠压告警状态
        DevDictionaries devDictionariesUUA = devDictionariesList.get(25);
        devDictionariesUUA.setName("Unit undervoltage alarm state");

        //充电电流告警状态
        DevDictionaries devDictionariesCCA = devDictionariesList.get(26);
        devDictionariesCCA.setName("Charging current alarm state");

        //放电电流告警状态
        DevDictionaries devDictionariesDCA = devDictionariesList.get(27);
        devDictionariesDCA.setName("Discharge current alarm state");

        //总体充电温度告警状态
        DevDictionaries devDictionariesOCTA = devDictionariesList.get(28);
        devDictionariesOCTA.setName("Overall charging temperature alarm state");

        //总体放电温度告警状态
        DevDictionaries devDictionariesODTA = devDictionariesList.get(29);
        devDictionariesODTA.setName("Overall discharge temperature alarm state");

        //电芯充电温度告警状态
        DevDictionaries devDictionariesBCTA = devDictionariesList.get(30);
        devDictionariesBCTA.setName("Battery charging temperature alarm state");

        //电芯放电温度告警状态
        DevDictionaries devDictionariesBDTA = devDictionariesList.get(31);
        devDictionariesBDTA.setName("Battery discharge temperature alarm state");

        //MOS温度告警状态
        DevDictionaries devDictionariesMTA = devDictionariesList.get(32);
        devDictionariesMTA.setName("MOS Temperature alarm state");

        //最大单体电压
        DevDictionaries devDictionariesMaxCV = devDictionariesList.get(33);
        devDictionariesMaxCV.setName("Maximum cell voltage");
        devDictionariesMaxCV.setUnit("mAh");

        //最小单体电压
        DevDictionaries devDictionariesMinxCV = devDictionariesList.get(34);
        devDictionariesMinxCV.setName("Minimum cell voltage");
        devDictionariesMinxCV.setUnit("mAh");

        //最大温度
        DevDictionaries devDictionariesMaxT = devDictionariesList.get(35);
        devDictionariesMaxT.setName("Max Temperature");
        devDictionariesMaxT.setUnit("℃");
        devDictionariesMaxT.setOperation("x*0.1-273.15");
        devDictionariesMaxT.setAnalysis("{\"valid\":\"2\"}");

        //最小温度
        DevDictionaries devDictionariesMinT = devDictionariesList.get(36);
        devDictionariesMinT.setName("Min Temperature");
        devDictionariesMinT.setUnit("℃");
        devDictionariesMinT.setOperation("x*0.1-273.15");
        devDictionariesMinT.setAnalysis("{\"valid\":\"2\"}");

        //电芯数量
        DevDictionaries devDictionariesCells = devDictionariesList.get(37);
        devDictionariesCells.setName("Number of cells");

        for (int i = 0; i < 16; i++) {
            DevDictionaries devDictionariesCV = devDictionariesList.get(38+i);
            devDictionariesCV.setName("Cells voltage"+(i+1));
            devDictionariesCV.setUnit("mV");
        }
        //电池温度
        for (int i = 0; i < 4; i++) {
            DevDictionaries devDictionarieT = devDictionariesList.get(55+i);
            devDictionarieT.setName("battery temperature"+(i+1));
            devDictionarieT.setUnit("℃");
            devDictionarieT.setOperation("x*0.1-273.15");
            devDictionarieT.setAnalysis("{\"valid\":\"2\"}");
        }
        ArrayList<DevDictionaries> devDictionaries = (ArrayList)DevSqlUtil.valueCompute(devDictionariesList);

//        devDictionariesMinT.setOperation("x*0.1-273.15");
//
        return devDictionaries;
    }

    public static void main(String[] args) {
//        String s="263;1;530;73;100;0;0;3;1;39204;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;3319;3313;2951;2941;16;3316;3315;3318;3319;3314;3318;3316;3316;3317;3315;3315;3315;3319;3316;3313;3316;4;2951;2951;2941;";
//        DevDictionariesServiceImpl devDictionariesService = new DevDictionariesServiceImpl();
//        ArrayList<DevDictionaries> devDictionaries = devDictionariesService.allDevData(s,"kk");
//        System.out.println(devDictionaries);

        System.out.println(Math.abs(Integer.parseInt(-1+"")) );
    }
}
